{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Run upon export from spreadsheet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "\n",
    "from astroquery.mast import Catalogs\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "\n",
    "tces_file = '/mnt/tess/labels/tois.csv'\n",
    "full_toi_file = '/mnt/tess/labels/full_toi_list.csv'\n",
    "ext_data_file = '/mnt/tess/labels/ext_mast_data.csv'\n",
    "\n",
    "\n",
    "tce_table = pd.read_csv(tces_file, header=0, low_memory=False)\n",
    "tce_table['tic_id'] = tce_table['TIC']\n",
    "tce_table['Duration'] = tce_table['Transit Duration Value']\n",
    "tce_table['Period'] = tce_table['Orbital Period Value']\n",
    "tce_table['RA'] = tce_table['TIC Right Ascension']\n",
    "tce_table['Sectors'] = tce_table['Sectors'].apply(lambda v: len(v.split(' ')))\n",
    "tce_table['Transit_Depth'] = tce_table['Transit Depth Value']\n",
    "tce_table['Dec'] = tce_table['TIC Declination']\n",
    "tce_table['teff'] = tce_table['Effective Temperature Value']\n",
    "tce_table['SN'] = tce_table['Signal-to-noise']\n",
    "tce_table['Qingress'] = 0.0\n",
    "tce_table['Tmag'] = tce_table['TMag Value']\n",
    "tce_table['logg'] = tce_table['Surface Gravity Value']\n",
    "tce_table['Epoc'] = tce_table['Epoch Value']\n",
    "tce_table['star_rad'] = tce_table['Star Radius Value']\n",
    "# G = 6.67e-8 in cgs\n",
    "tce_table['star_mass'] = ((10 ** tce_table['logg']) * (tce_table['star_rad'] ** 2)) / 6.67e-8\n",
    "tce_table = tce_table.set_index('tic_id')\n",
    "\n",
    "tce_table['Duration'] /= 24.0\n",
    "\n",
    "tce_table = tce_table.reset_index()[[\n",
    "    'tic_id', 'RA', 'Dec', 'Tmag', 'Epoc', 'Period', 'Duration',\n",
    "    'Transit_Depth', 'Sectors', 'star_rad', 'star_mass', 'teff',\n",
    "    'logg', 'SN', 'Qingress'\n",
    "]].set_index('tic_id')\n",
    "\n",
    "\n",
    "full_toi_table = pd.read_csv(full_toi_file, header=1, low_memory=False)\n",
    "full_toi_table = full_toi_table.set_index('tic_id')\n",
    "full_toi_table[~full_toi_table['duration_bls'].isna()]\n",
    "\n",
    "\n",
    "bls_table = full_toi_table.reset_index()[[\n",
    "    'tic_id',\n",
    "    'star_mass_bls',\n",
    "    'star_rad_bls',\n",
    "    'epoch_bls',\n",
    "    'period_bls',\n",
    "    'depth_bls',\n",
    "    'duration_bls',\n",
    "    'tmag_bls',\n",
    "    'Signal-to-noise',\n",
    "]].set_index('tic_id')\n",
    "\n",
    "joined_table = tce_table.join(bls_table, on='tic_id', how='inner')\n",
    "joined_table = joined_table[~joined_table['epoch_bls'].isna()]\n",
    "\n",
    "joined_table['star_mass'] = joined_table['star_mass_bls']\n",
    "joined_table['star_rad'] = joined_table['star_rad_bls']\n",
    "joined_table['Epoc'] = joined_table['epoch_bls']\n",
    "joined_table['Period'] = joined_table['period_bls']\n",
    "joined_table['Transit_Depth'] = joined_table['depth_bls']\n",
    "joined_table['Duration'] = joined_table['duration_bls']\n",
    "joined_table['Tmag'] = joined_table['tmag_bls']\n",
    "joined_table['SN'] = joined_table['Signal-to-noise']\n",
    "\n",
    "joined_table = joined_table.reset_index()[[\n",
    "    'tic_id', 'RA', 'Dec', 'Tmag', 'Epoc', 'Period', 'Duration',\n",
    "    'Transit_Depth', 'Sectors', 'star_rad', 'star_mass', 'teff',\n",
    "    'logg', 'SN', 'Qingress'\n",
    "]]\n",
    "joined_table['Source'] = 2\n",
    "\n",
    "final_table = joined_table\n",
    "\n",
    "tev_table = full_toi_table.reset_index()[[\n",
    "    'tic_id',\n",
    "    'star_mass_bls',\n",
    "    'star_rad_bls',\n",
    "    'epoch_tev',\n",
    "    'period_tev',\n",
    "    'depth_tev',\n",
    "    'duration_tev',\n",
    "    'tmag_tev',\n",
    "    'Signal-to-noise',\n",
    "]].set_index('tic_id')\n",
    "\n",
    "joined_table = tce_table.join(tev_table, on='tic_id', how='inner')\n",
    "joined_table = joined_table[~joined_table['epoch_tev'].isna()]\n",
    "\n",
    "joined_table['star_mass'] = joined_table['star_mass_bls']\n",
    "joined_table['star_rad'] = joined_table['star_rad_bls']\n",
    "joined_table['Epoc'] = joined_table['epoch_tev']\n",
    "joined_table['Period'] = joined_table['period_tev']\n",
    "joined_table['Transit_Depth'] = joined_table['depth_tev']\n",
    "joined_table['Duration'] = joined_table['duration_tev']\n",
    "joined_table['Tmag'] = joined_table['tmag_tev']\n",
    "joined_table['SN'] = joined_table['Signal-to-noise']\n",
    "\n",
    "joined_table['Duration'] /= 24.0\n",
    "\n",
    "joined_table = joined_table.reset_index()[[\n",
    "    'tic_id', 'RA', 'Dec', 'Tmag', 'Epoc', 'Period', 'Duration',\n",
    "    'Transit_Depth', 'Sectors', 'star_rad', 'star_mass', 'teff',\n",
    "    'logg', 'SN', 'Qingress'\n",
    "]]\n",
    "joined_table['Source'] = 3\n",
    "\n",
    "final_table = final_table.append(joined_table)\n",
    "\n",
    "final_table.to_csv('/mnt/tess/astronet/tces-toi-bls-vs-tev.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "python astronet/preprocess/generate_input_records.py --input_tce_csv_file=/mnt/tess/astronet/tces-toi-bls-vs-tev --tess_data_dir=/mnt/tess/lc --output_dir=/mnt/tess/astronet/tfrecords-toi-bls-vs-tev --num_shards=1\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>tic_id</th>\n",
       "      <th>RA</th>\n",
       "      <th>Dec</th>\n",
       "      <th>Tmag</th>\n",
       "      <th>Epoc</th>\n",
       "      <th>Period</th>\n",
       "      <th>Duration</th>\n",
       "      <th>Transit_Depth</th>\n",
       "      <th>Sectors</th>\n",
       "      <th>star_rad</th>\n",
       "      <th>star_mass</th>\n",
       "      <th>teff</th>\n",
       "      <th>logg</th>\n",
       "      <th>SN</th>\n",
       "      <th>Qingress</th>\n",
       "      <th>Source</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>323</th>\n",
       "      <td>161477033</td>\n",
       "      <td>73.260995</td>\n",
       "      <td>-45.540671</td>\n",
       "      <td>10.061</td>\n",
       "      <td>2193.3292</td>\n",
       "      <td>40.8923</td>\n",
       "      <td>0.180000</td>\n",
       "      <td>1050.00</td>\n",
       "      <td>4</td>\n",
       "      <td>0.879477</td>\n",
       "      <td>0.91</td>\n",
       "      <td>5465.0</td>\n",
       "      <td>4.57025</td>\n",
       "      <td>12.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>324</th>\n",
       "      <td>161477033</td>\n",
       "      <td>73.260995</td>\n",
       "      <td>-45.540671</td>\n",
       "      <td>10.061</td>\n",
       "      <td>2193.3292</td>\n",
       "      <td>40.8923</td>\n",
       "      <td>0.180000</td>\n",
       "      <td>1050.00</td>\n",
       "      <td>2</td>\n",
       "      <td>0.879477</td>\n",
       "      <td>0.91</td>\n",
       "      <td>5465.0</td>\n",
       "      <td>4.57025</td>\n",
       "      <td>12.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>325</th>\n",
       "      <td>161477033</td>\n",
       "      <td>73.260995</td>\n",
       "      <td>-45.540671</td>\n",
       "      <td>10.061</td>\n",
       "      <td>2193.3292</td>\n",
       "      <td>40.8923</td>\n",
       "      <td>0.180000</td>\n",
       "      <td>1050.00</td>\n",
       "      <td>1</td>\n",
       "      <td>0.879477</td>\n",
       "      <td>0.91</td>\n",
       "      <td>5465.0</td>\n",
       "      <td>4.57025</td>\n",
       "      <td>12.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>697</th>\n",
       "      <td>161477033</td>\n",
       "      <td>73.260995</td>\n",
       "      <td>-45.540671</td>\n",
       "      <td>10.018</td>\n",
       "      <td>1419.3700</td>\n",
       "      <td>11.9280</td>\n",
       "      <td>0.119167</td>\n",
       "      <td>730.16</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5465.0</td>\n",
       "      <td>4.57025</td>\n",
       "      <td>8.55</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>698</th>\n",
       "      <td>161477033</td>\n",
       "      <td>73.260995</td>\n",
       "      <td>-45.540671</td>\n",
       "      <td>10.018</td>\n",
       "      <td>1443.2220</td>\n",
       "      <td>14.0580</td>\n",
       "      <td>0.146250</td>\n",
       "      <td>858.62</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5465.0</td>\n",
       "      <td>4.57025</td>\n",
       "      <td>8.82</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>699</th>\n",
       "      <td>161477033</td>\n",
       "      <td>73.260995</td>\n",
       "      <td>-45.540671</td>\n",
       "      <td>10.018</td>\n",
       "      <td>2193.3160</td>\n",
       "      <td>20.4450</td>\n",
       "      <td>0.155833</td>\n",
       "      <td>920.00</td>\n",
       "      <td>4</td>\n",
       "      <td>0.879477</td>\n",
       "      <td>0.91</td>\n",
       "      <td>5465.0</td>\n",
       "      <td>4.57025</td>\n",
       "      <td>12.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>700</th>\n",
       "      <td>161477033</td>\n",
       "      <td>73.260995</td>\n",
       "      <td>-45.540671</td>\n",
       "      <td>10.018</td>\n",
       "      <td>1419.3700</td>\n",
       "      <td>11.9280</td>\n",
       "      <td>0.119167</td>\n",
       "      <td>730.16</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5465.0</td>\n",
       "      <td>4.57025</td>\n",
       "      <td>8.55</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>701</th>\n",
       "      <td>161477033</td>\n",
       "      <td>73.260995</td>\n",
       "      <td>-45.540671</td>\n",
       "      <td>10.018</td>\n",
       "      <td>1443.2220</td>\n",
       "      <td>14.0580</td>\n",
       "      <td>0.146250</td>\n",
       "      <td>858.62</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5465.0</td>\n",
       "      <td>4.57025</td>\n",
       "      <td>8.82</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>702</th>\n",
       "      <td>161477033</td>\n",
       "      <td>73.260995</td>\n",
       "      <td>-45.540671</td>\n",
       "      <td>10.018</td>\n",
       "      <td>2193.3160</td>\n",
       "      <td>20.4450</td>\n",
       "      <td>0.155833</td>\n",
       "      <td>920.00</td>\n",
       "      <td>2</td>\n",
       "      <td>0.879477</td>\n",
       "      <td>0.91</td>\n",
       "      <td>5465.0</td>\n",
       "      <td>4.57025</td>\n",
       "      <td>12.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>703</th>\n",
       "      <td>161477033</td>\n",
       "      <td>73.260995</td>\n",
       "      <td>-45.540671</td>\n",
       "      <td>10.018</td>\n",
       "      <td>1419.3700</td>\n",
       "      <td>11.9280</td>\n",
       "      <td>0.119167</td>\n",
       "      <td>730.16</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5465.0</td>\n",
       "      <td>4.57025</td>\n",
       "      <td>8.55</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>704</th>\n",
       "      <td>161477033</td>\n",
       "      <td>73.260995</td>\n",
       "      <td>-45.540671</td>\n",
       "      <td>10.018</td>\n",
       "      <td>1443.2220</td>\n",
       "      <td>14.0580</td>\n",
       "      <td>0.146250</td>\n",
       "      <td>858.62</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5465.0</td>\n",
       "      <td>4.57025</td>\n",
       "      <td>8.82</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>705</th>\n",
       "      <td>161477033</td>\n",
       "      <td>73.260995</td>\n",
       "      <td>-45.540671</td>\n",
       "      <td>10.018</td>\n",
       "      <td>2193.3160</td>\n",
       "      <td>20.4450</td>\n",
       "      <td>0.155833</td>\n",
       "      <td>920.00</td>\n",
       "      <td>1</td>\n",
       "      <td>0.879477</td>\n",
       "      <td>0.91</td>\n",
       "      <td>5465.0</td>\n",
       "      <td>4.57025</td>\n",
       "      <td>12.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        tic_id         RA        Dec    Tmag       Epoc   Period  Duration  \\\n",
       "323  161477033  73.260995 -45.540671  10.061  2193.3292  40.8923  0.180000   \n",
       "324  161477033  73.260995 -45.540671  10.061  2193.3292  40.8923  0.180000   \n",
       "325  161477033  73.260995 -45.540671  10.061  2193.3292  40.8923  0.180000   \n",
       "697  161477033  73.260995 -45.540671  10.018  1419.3700  11.9280  0.119167   \n",
       "698  161477033  73.260995 -45.540671  10.018  1443.2220  14.0580  0.146250   \n",
       "699  161477033  73.260995 -45.540671  10.018  2193.3160  20.4450  0.155833   \n",
       "700  161477033  73.260995 -45.540671  10.018  1419.3700  11.9280  0.119167   \n",
       "701  161477033  73.260995 -45.540671  10.018  1443.2220  14.0580  0.146250   \n",
       "702  161477033  73.260995 -45.540671  10.018  2193.3160  20.4450  0.155833   \n",
       "703  161477033  73.260995 -45.540671  10.018  1419.3700  11.9280  0.119167   \n",
       "704  161477033  73.260995 -45.540671  10.018  1443.2220  14.0580  0.146250   \n",
       "705  161477033  73.260995 -45.540671  10.018  2193.3160  20.4450  0.155833   \n",
       "\n",
       "     Transit_Depth  Sectors  star_rad  star_mass    teff     logg     SN  \\\n",
       "323        1050.00        4  0.879477       0.91  5465.0  4.57025  12.00   \n",
       "324        1050.00        2  0.879477       0.91  5465.0  4.57025  12.00   \n",
       "325        1050.00        1  0.879477       0.91  5465.0  4.57025  12.00   \n",
       "697         730.16        4       NaN        NaN  5465.0  4.57025   8.55   \n",
       "698         858.62        4       NaN        NaN  5465.0  4.57025   8.82   \n",
       "699         920.00        4  0.879477       0.91  5465.0  4.57025  12.00   \n",
       "700         730.16        2       NaN        NaN  5465.0  4.57025   8.55   \n",
       "701         858.62        2       NaN        NaN  5465.0  4.57025   8.82   \n",
       "702         920.00        2  0.879477       0.91  5465.0  4.57025  12.00   \n",
       "703         730.16        1       NaN        NaN  5465.0  4.57025   8.55   \n",
       "704         858.62        1       NaN        NaN  5465.0  4.57025   8.82   \n",
       "705         920.00        1  0.879477       0.91  5465.0  4.57025  12.00   \n",
       "\n",
       "     Qingress  Source  \n",
       "323       0.0       2  \n",
       "324       0.0       2  \n",
       "325       0.0       2  \n",
       "697       0.0       3  \n",
       "698       0.0       3  \n",
       "699       0.0       3  \n",
       "700       0.0       3  \n",
       "701       0.0       3  \n",
       "702       0.0       3  \n",
       "703       0.0       3  \n",
       "704       0.0       3  \n",
       "705       0.0       3  "
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_table[final_table.tic_id == 161477033]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>tic_id</th>\n",
       "      <th>RA</th>\n",
       "      <th>Dec</th>\n",
       "      <th>Tmag</th>\n",
       "      <th>Epoc</th>\n",
       "      <th>Period</th>\n",
       "      <th>Duration</th>\n",
       "      <th>Transit_Depth</th>\n",
       "      <th>Sectors</th>\n",
       "      <th>star_rad</th>\n",
       "      <th>star_mass</th>\n",
       "      <th>teff</th>\n",
       "      <th>logg</th>\n",
       "      <th>SN</th>\n",
       "      <th>Qingress</th>\n",
       "      <th>Source</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>52368076</td>\n",
       "      <td>23.594697</td>\n",
       "      <td>-66.675830</td>\n",
       "      <td>10.138</td>\n",
       "      <td>1334.4570</td>\n",
       "      <td>9.165477</td>\n",
       "      <td>0.141667</td>\n",
       "      <td>923.85</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5154.0</td>\n",
       "      <td>4.47072</td>\n",
       "      <td>7.75</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>135</th>\n",
       "      <td>207237016</td>\n",
       "      <td>49.816355</td>\n",
       "      <td>-59.400160</td>\n",
       "      <td>11.246</td>\n",
       "      <td>1365.0031</td>\n",
       "      <td>26.412900</td>\n",
       "      <td>0.100000</td>\n",
       "      <td>6120.00</td>\n",
       "      <td>5</td>\n",
       "      <td>2.628660</td>\n",
       "      <td>1.130</td>\n",
       "      <td>6070.0</td>\n",
       "      <td>3.65000</td>\n",
       "      <td>23.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>591</th>\n",
       "      <td>177722855</td>\n",
       "      <td>107.421556</td>\n",
       "      <td>-11.328950</td>\n",
       "      <td>10.522</td>\n",
       "      <td>1492.2670</td>\n",
       "      <td>2.389330</td>\n",
       "      <td>0.201250</td>\n",
       "      <td>660.00</td>\n",
       "      <td>1</td>\n",
       "      <td>1.370310</td>\n",
       "      <td>1.026</td>\n",
       "      <td>5743.0</td>\n",
       "      <td>4.18000</td>\n",
       "      <td>16.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>219388773</td>\n",
       "      <td>77.047581</td>\n",
       "      <td>-50.858766</td>\n",
       "      <td>10.679</td>\n",
       "      <td>1412.3830</td>\n",
       "      <td>1.571527</td>\n",
       "      <td>0.060000</td>\n",
       "      <td>720.00</td>\n",
       "      <td>1</td>\n",
       "      <td>0.817898</td>\n",
       "      <td>0.760</td>\n",
       "      <td>4752.8</td>\n",
       "      <td>4.37000</td>\n",
       "      <td>7.35</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1006</th>\n",
       "      <td>344085117</td>\n",
       "      <td>108.017394</td>\n",
       "      <td>-50.247688</td>\n",
       "      <td>11.973</td>\n",
       "      <td>1541.8050</td>\n",
       "      <td>0.659700</td>\n",
       "      <td>0.027917</td>\n",
       "      <td>960.00</td>\n",
       "      <td>1</td>\n",
       "      <td>1.251120</td>\n",
       "      <td>1.050</td>\n",
       "      <td>5832.0</td>\n",
       "      <td>4.26000</td>\n",
       "      <td>12.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         tic_id          RA        Dec    Tmag       Epoc     Period  \\\n",
       "67     52368076   23.594697 -66.675830  10.138  1334.4570   9.165477   \n",
       "135   207237016   49.816355 -59.400160  11.246  1365.0031  26.412900   \n",
       "591   177722855  107.421556 -11.328950  10.522  1492.2670   2.389330   \n",
       "122   219388773   77.047581 -50.858766  10.679  1412.3830   1.571527   \n",
       "1006  344085117  108.017394 -50.247688  11.973  1541.8050   0.659700   \n",
       "\n",
       "      Duration  Transit_Depth  Sectors  star_rad  star_mass    teff     logg  \\\n",
       "67    0.141667         923.85        3       NaN        NaN  5154.0  4.47072   \n",
       "135   0.100000        6120.00        5  2.628660      1.130  6070.0  3.65000   \n",
       "591   0.201250         660.00        1  1.370310      1.026  5743.0  4.18000   \n",
       "122   0.060000         720.00        1  0.817898      0.760  4752.8  4.37000   \n",
       "1006  0.027917         960.00        1  1.251120      1.050  5832.0  4.26000   \n",
       "\n",
       "         SN  Qingress  Source  \n",
       "67     7.75       0.0       3  \n",
       "135   23.00       0.0       2  \n",
       "591   16.00       0.0       3  \n",
       "122    7.35       0.0       2  \n",
       "1006  12.00       0.0       3  "
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.set_option('display.max_columns', None)\n",
    "final_table.sample(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Run once"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "def load_tces_old():\n",
    "    tceold = pd.read_csv('/mnt/tess/astronet/tces.csv', header=0).set_index('tic_id')\n",
    "\n",
    "    # Only keep the max sectors read.\n",
    "    maxsect = tceold.groupby('tic_id')['Sectors'].max()\n",
    "    tceold = tceold.join(maxsect, on='tic_id', how='right', rsuffix='_max')\n",
    "    tceold = tceold[tceold.Sectors == tceold.Sectors_max]\n",
    "\n",
    "    # Then keep the max row ID.\n",
    "    maxrowid = tceold.groupby('tic_id')['row_id'].max()\n",
    "    tceold = tceold.join(maxrowid, on='tic_id', how='right', rsuffix='_max')\n",
    "    tceold = tceold[tceold.row_id == tceold.row_id_max]\n",
    "\n",
    "    return tceold\n",
    "\n",
    "def generate_tce_bls_instar():\n",
    "    tcenew = pd.read_csv('/mnt/tess/labels/tce_bls_instar.csv', header=0).set_index('tic_id')\n",
    "    tceold = load_tces_old()\n",
    "    tcenorth = pd.read_csv('/mnt/tess/labels/tce_north_instar.csv', header=0).set_index('tic_id')\n",
    "\n",
    "    # Copy from old data where it's missing from the new.\n",
    "    alltce = tcenew.join(tceold, how='outer', on='tic_id', rsuffix='_old')\n",
    "    alltce = alltce.set_index('tic_id')\n",
    "\n",
    "    alltce = alltce.drop(columns=['row_id'])\n",
    "\n",
    "    def fillna(df, col_name):\n",
    "        df.loc[df[col_name].isna(), col_name] = df.loc[df[col_name].isna(), col_name + '_old']\n",
    "\n",
    "    fillna(alltce, 'toi_id')\n",
    "    fillna(alltce, 'Disposition')\n",
    "    fillna(alltce, 'RA')\n",
    "    fillna(alltce, 'Dec')\n",
    "    fillna(alltce, 'Tmag')\n",
    "    fillna(alltce, 'Epoc')\n",
    "    fillna(alltce, 'Period')\n",
    "    fillna(alltce, 'Duration')\n",
    "    fillna(alltce, 'Transit_Depth')\n",
    "    fillna(alltce, 'Sectors')\n",
    "    fillna(alltce, 'camera')\n",
    "    fillna(alltce, 'ccd')\n",
    "    fillna(alltce, 'star_rad')\n",
    "    fillna(alltce, 'star_mass')\n",
    "    fillna(alltce, 'teff')\n",
    "    fillna(alltce, 'logg')\n",
    "    fillna(alltce, 'SN')\n",
    "    fillna(alltce, 'Qingress')\n",
    "\n",
    "    alltce = alltce.drop(columns=[c for c in alltce.columns if c.endswith('_old')])\n",
    "    \n",
    "    alltce = alltce.append(tcenorth)\n",
    "    \n",
    "    alltce['Ilabel'] = alltce['Ilabel'].fillna(False)\n",
    "\n",
    "    alltce.to_csv('/mnt/tess/labels/tce_bls_instar+old.csv')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
